package com.bestow.dox4j.utils;

import com.bestow.basic.domain.HlsDbDataSource;
import com.bestow.basic.domain.HlsDbDataSourceColumn;
import com.bestow.basic.mapper.HlsDbDataSourceColumnMapper;
import com.bestow.basic.mapper.HlsDbDataSourceMapper;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.util.LinkedCaseInsensitiveMap;
import org.springframework.util.StringUtils;

import javax.validation.constraints.NotNull;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Component
public class Datasource2Json {

    @Autowired
    private HlsDbDataSourceMapper dsMapper;

    /*@Autowired
    private HlsDbDataSourceParaMapper paraMapper;*/

    /*@Autowired
    private HlsDbDataSourceTbMapper tableMapper;*/

    /*@Autowired
    private HlsDbDataSourceTbWhMapper whereMapper;*/

    @Autowired
    private HlsDbDataSourceColumnMapper columnMapper;
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedJdbcTemplate;

    private String dbName;

    public String getDbName() {
        return dbName;
    }

    public void setDbName(String dbName) {
        this.dbName = dbName;
    }

    /*public String createSQL(long dataSourceId) {
        // 获取dataSourceID对应的所有的启用参数
        HlsDbDataSourcePara param = new HlsDbDataSourcePara();
        param.setDataSourceId(dataSourceId);
        param.setEnabledFlag("Y" );
        List<HlsDbDataSourcePara> paramList = paraMapper.select(param);

        // 获取dataSourceId对应的所有的启用表
        HlsDbDataSourceTb table = new HlsDbDataSourceTb();
        table.setDataSourceId(dataSourceId);
        table.setEnabledFlag("Y" );
        List<HlsDbDataSourceTb> tableList = tableMapper.select(table);

        return generateSQL(dataSourceId, paramList, tableList);
    }*/

    public String executeSQLs4Json(List<Long> dsIds, Map<String, Object> params) throws IOException {
        if (dsIds == null) {
            return "";
        }
        ObjectMapper mapper = new ObjectMapper();
        HashMap<String, Object> map = new HashMap<>(dsIds.size());
        for (Long dsId : dsIds) {
            Map<String, Object> param = null;
            if (params != null && params.containsKey(dsId.toString())) {
                param = (Map<String, Object>) params.get(dsId.toString());
            }
            String s = executeSQL4Json(dsId, param);
            HashMap hashMap = mapper.readValue(s, HashMap.class);
            if (hashMap.containsKey(dsId.toString())) {
                map.put(dsId.toString(), hashMap.get(dsId.toString()));
            }
        }
        return mapper.writeValueAsString(map);
    }

    public String executeSQLs4Json(List<Long> dsIds) throws IOException {
        return executeSQLs4Json(dsIds, null);
    }

    public String executeSQL4Json(Long dataSourceId) throws IOException {
        return executeSQL4Json(dataSourceId, null);
    }

    public String executeSQL4Json(Long dataSourceId, Map<String, Object> params) throws IOException {
        HlsDbDataSource ds = dsMapper.selectHlsDbDataSourceByDataSourceId(dataSourceId);
        if (ds != null) {
            String type = ds.getUsageType();
            return executeSQL4Json(dataSourceId, type, SqlUtils.toMySQL(ds.getSqlContext()), params);
        }
        return "";
    }

    public List<Map<String, Object>> executeSQL4List(Long dataSourceId, Map<String, Object> params) {
        HlsDbDataSource ds = dsMapper.selectHlsDbDataSourceByDataSourceId(dataSourceId);
        if (ds != null) {
            return executeSQL(dataSourceId, ds.getSqlContext(), params);
        }
        return null;
    }

    private String executeSQL4Json(Long dsId, String type, String sql, Map<String, Object> params) throws IOException {
        List<Map<String, Object>> maps = executeSQL(dsId, sql, params);

        HashMap<String, Object> map = new HashMap<>();
        if ("BOOKMARK".equalsIgnoreCase(type)) {
            HashMap<String, Object> table = getBookmarkMap(dsId);
            table.put("table" , maps);
            map.put(dsId.toString(), table);
        } else {
            HashMap<String, Object> dft = new HashMap<>();
            dft.put("default" , maps.stream().findFirst().orElse(new HashMap<>()));
            map.put(dsId.toString(), dft);
        }

        ObjectMapper mapper = new ObjectMapper();
        return mapper.writeValueAsString(map);
    }

    private List<Map<String, Object>> executeSQL(Long dataSourceId, String sql, Map<String, Object> params) {
        if (StringUtils.isEmpty(sql))
            return new ArrayList<>(0);
        // 先处理sql，将参数转换为jdbcTemplate支持的占位符
        if (params != null) {
            Set<Map.Entry<String, Object>> entries = params.entrySet();
            for (Map.Entry<String, Object> entry : entries) {
                String key = entry.getKey();
                sql = sql.replace("${" + key + "}" , ":" + key);
            }
        }

        HlsDbDataSourceColumn hlsDbDataSourceColumn = new HlsDbDataSourceColumn();
        hlsDbDataSourceColumn.setDataSourceId(dataSourceId);
        hlsDbDataSourceColumn.setEnabledFlag("Y" );
        List<HlsDbDataSourceColumn> columns = columnMapper.selectHlsDbDataSourceColumnList(hlsDbDataSourceColumn);

        List<Map<String, Object>> maps = namedJdbcTemplate.queryForList(SqlUtils.toMySQL(sql), params);
        if (maps == null || maps.size() < 1) {
            return new ArrayList<>(0);
        }

        if (columns != null && columns.size() > 0) {

            maps = trans(maps, columns);
        }
        return maps;
    }

    private List<Map<String, Object>> trans(@NotNull List<Map<String, Object>> maps, @NotNull List<HlsDbDataSourceColumn> columns) {

        ArrayList<Map<String, Object>> result = new ArrayList<>(maps.size());
        for (Map<String, Object> map : maps) {
            if (!(map instanceof LinkedCaseInsensitiveMap)) {
                continue;
            }
            HashMap<String, Object> resultMap = new HashMap<>(map.size());
            for (HlsDbDataSourceColumn column : columns) {
                String aliasName = column.getAliasName();
                String columnName = column.getColumnName();
                if ("double".equals(column.getColumnType())) {
                    BigDecimal bigDecimal = new BigDecimal(map.get(columnName).toString());
                    bigDecimal.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                    String amount = bigDecimal.toPlainString();
                    resultMap.put(aliasName, amount);
                } else {
                    if (isNumeric(String.valueOf(map.get(columnName)))) {
                        BigDecimal bigDecimal = new BigDecimal(map.get(columnName).toString());
                        bigDecimal.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                        String amount = bigDecimal.toPlainString();
                        resultMap.put(aliasName, amount);
                    } else {
                        resultMap.put(aliasName, map.get(columnName));
                    }
                }
            }
            result.add(resultMap);
        }
        return result;
    }

    private HashMap<String, Object> getBookmarkMap(Long dsId) {
        return new HashMap<>();
    }

    public List<String> getAllTables() {
        if (dbName == null || "".equals(dbName.trim())) {
            return new ArrayList<>(0);
        }
        String sql = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='" + dbName + "'";
        //FIXME oracle下可能有大小写问题
        List<String> tableNames = jdbcTemplate.queryForList(sql, String.class);
        return tableNames;
    }

    public List<String> getAllColumns(String tableName) {
        if (dbName == null || "".equals(dbName.trim()) || tableName == null || "".equals(tableName.trim())) {
            return new ArrayList<>(0);
        }
        String sql = "select COLUMN_NAME from INFORMATION_SCHEMA.Columns where table_name='"
                + tableName
                + "' and table_schema="
                + dbName;
        //+ "'";
        //FIXME oracle下可能有大小写问题
        List<String> columns = jdbcTemplate.queryForList(sql, String.class);
        return columns;
    }

    /*private String generateSQL(long dataSourceId, List<HlsDbDataSourcePara> paramList, List<HlsDbDataSourceTb> tableList) {
        String sql = "";
        if (tableList == null || paramList == null || tableList.size() < 1) {
            return sql;
        }
        StringBuilder select = new StringBuilder("select " );
        StringBuilder on = new StringBuilder();
        StringBuilder from = new StringBuilder();
        StringBuilder where = new StringBuilder();
        for (int i = 0; i < tableList.size(); i++) {
            HlsDbDataSourceTb table = tableList.get(i);
            String tableName = table.getDbObjectName();
            String aliasName = table.getAliasName();
            String baseTableFlag = table.getBaseTableFlag();

            // 获取每个表的结果列
            String sp = generateSelectPartByTable(dataSourceId, table);
            if (sp != null) {
                select.append(sp);
                if (i < tableList.size() - 1) {
                    select.append("," );
                }
            }
            if ("Y".equalsIgnoreCase(baseTableFlag)) {
                // 基础表
                from.append(" from " ).append(tableName).append(" as " ).append(aliasName);
                // where 语句
                where.append(" where " );
            } else {
                // 连接表
                String joinType = table.getJoinType();
                on.append(joinType).append(" " ).append(tableName).append(" as " ).append(aliasName);
                on.append(" on " );
            }
        }
        return "";
    }*/

    /*private String generateSelectPartByTable(@NotNull Long dsId, @NotNull HlsDbDataSourceTb table) {
        if (table == null || dsId == null) {
            throw new IllegalArgumentException("arguements cannot be null!" );
        }
        List<HlsDbDataSourceColumn> columns = getColumnByDsTable(dsId, table.getDataSourceTbId());
        StringBuilder sb = new StringBuilder();
        if (columns == null || columns.size() < 1) {
            return null;
        }
        String aliasName = table.getAliasName();
        for (int i = 0; i < columns.size(); i++) {
            HlsDbDataSourceColumn column = columns.get(i);
            sb.append(aliasName).append("." ).append(column.getColumnName()).append(" as " ).append(column.getAliasName());
            if (i < columns.size() - 1) {
                sb.append("," );
            }
        }
        return sb.toString();
    }*/

    /**
     * 根据table生成From语句部分
     *
     * @param table
     * @return
     */
    /*private String generateFromOrOnPartByTable(@NotNull HlsDbDataSourceTb table) {
        if (table == null) {
            throw new IllegalArgumentException("arguements cannot be null!" );
        }
        StringBuilder sb = new StringBuilder(" from " );
        sb.append(table.getDbObjectName());
        if (table.getAliasName() == null || "".equals(table.getAliasName().trim())) {
            sb.append(" as " ).append(table.getAliasName());
        }
        return sb.toString();
    }*/

    /*private List<HlsDbDataSourceTbWh> getWhereByTable(long tableId) {
        HlsDbDataSourceTbWh where = new HlsDbDataSourceTbWh();
        where.setDataSourceTbId(tableId);
        where.setEnabledFlag("Y" );
        return whereMapper.select(where);
    }*/

    /*private List<HlsDbDataSourceColumn> getColumnByDsTable(long dsId, long tableId) {
        HlsDbDataSourceColumn column = new HlsDbDataSourceColumn();
        column.setDataSourceId(dsId);
        column.setDataSourceTbId(tableId);
        column.setEnabledFlag("Y" );
        return columnMapper.select(column);
    }*/

    /**
     * 将参数列表转成Map集合，key为参数的id
     *
     * @param paramList 参数列表
     * @return 转换完成的Map集合
     */
    /*private Map<Long, HlsDbDataSourcePara> convertPara(List<HlsDbDataSourcePara> paramList) {
        HashMap<Long, HlsDbDataSourcePara> paraMap = new HashMap<>();
        if (paramList != null) {
            for (HlsDbDataSourcePara param : paramList) {
                if (param != null && param.getDataSourceId() != null) {
                    paraMap.put(param.getDataSourceParaId(), param);
                }
            }
        }
        return paraMap;
    }*/

    /**
     * 判断一个字符是否是科学计数法
     *
     * @param str
     * @return
     */
    static String regx = "^((-?\\d+.?\\d*)[Ee]{1}(-?\\d+))$";

    public static boolean isNumeric(String str) {
        //如果是数字，创建new BigDecimal()时肯定不会报错，那就可以直接返回true
        String bigdecimal;

        Pattern pattern = Pattern.compile(regx);
        Matcher matcher = pattern.matcher(str);
        boolean rs = matcher.matches();

        return rs;
    }
}
